﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using MainProgram.ModelReport;

namespace MainProgram
{
    class SqlController
    {
        private string connectionString;
        SqlConnection myConnection = new SqlConnection();
        SqlCommand myCommand = new SqlCommand();
        DataSetReport dataSet = new DataSetReport();
        SqlDataAdapter dataAdapter = new SqlDataAdapter();

        public SqlController(){}

        public SqlController(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void SetConnectionString(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void Open()
        {
            try
            {
                myConnection.ConnectionString = connectionString;
                myConnection.Open();
                myCommand.Connection = myConnection;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void Close()
        {
            myConnection.Close();
        }

        public SqlDataAdapter GetDataAdapter(string commandString)
        {
            return new SqlDataAdapter(commandString, myConnection);
        }

        public DataSetReport GetDataSet(string commandString, string DataTableString)
        {
            dataAdapter.Dispose();
            dataSet.Clear();
            dataAdapter = new SqlDataAdapter(commandString, myConnection);
            dataAdapter.Fill(dataSet, DataTableString);
            return dataSet;
        }

        public string GetFirstString(string commandString,string columnString)
        {
            myCommand.CommandText = commandString;
            SqlDataReader myDataReader = myCommand.ExecuteReader();
            myDataReader.Read();
            string tempString = myDataReader[columnString].ToString();
            myDataReader.Close();
            return tempString;
        }

        public ConnectionState GetState()
        {
            return myConnection.State;
        }

        public void Update(string dataTableString)
        {
            SqlCommandBuilder scb = new SqlCommandBuilder(dataAdapter);
            dataAdapter.Update(dataSet, dataTableString);
        }
    }
}
